﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_AssemblyCheck : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private int checktype;
        private DataTable checkfunction;
        public panel_AssemblyCheck()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.checktype = 0;
            this.checkfunction = new DataTable();
            this.checkfunction.Columns.Add("FunctionID", typeof(int));
            this.checkfunction.Columns.Add("CheckTypeID", typeof(int));
            this.checkfunction.Columns.Add("FunctionName", typeof(string));
            this.buttonExport.Enabled = false;
            this.buttonRefresh.Enabled = false;
            this.bottonAdd.Enabled = false;
            this.bottonEdit.Enabled = false;
            LoadCheckType();
            this.AssemblyCheck.DoubleClick += RV201_DoubleClick;
            this.AssemblyCheck.PopupMenuShowing += RV201_PopupMenuShowing;
            this.assemblyCheckMenu.ItemClicked += f4RV201ContextMenu_ItemClicked;
        }

        //////////////////////////////////////
        /// Load Check Type to List
        //////////////////////////////////////
        private void LoadCheckType()
        {
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT * FROM [231_CheckType] ORDER BY CheckTypeName";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Value = dataReader["CheckTypeID"].ToString().Trim();
                    item.Text = dataReader["CheckTypeName"].ToString().Trim();
                    this.checkTypeID.Properties.Items.Add(item);
                }
                dataReader.Close();
                sql_cmd.Dispose();
            }
            catch
            {
                MessageBox.Show("Cannot load Check Type");
            }
            finally
            {
                myConnection.Close();
            }
        }

        //////////////////////////////
        /// Call Add Form
        //////////////////////////////
        private void callAddForm()
        {
            if (this.checkTypeID.SelectedIndex < 0)
            {
                MessageBox.Show("Please select Check Type to add details");
            }
            else
            {
                ComboboxItem item = (ComboboxItem)this.checkTypeID.SelectedItem;
                frm_Add_AssemblyCheck add_form = new frm_Add_AssemblyCheck();
                add_form.checkType = item;
                if (add_form.CreateForm())
                {
                    add_form.ShowDialog();
                    LoadData();
                }
                else
                {
                    MessageBox.Show("Cannot add new details.");
                    add_form.Dispose();
                }
            }
        }

        /////////////////////////////
        /// Call Edit Form
        /////////////////////////////
        private void callEditForm()
        {
            if (this.checkTypeID.SelectedIndex < 0)
            {
                MessageBox.Show("Please select Check Type to add details");
            }
            else
            {
                ComboboxItem item = (ComboboxItem)this.checkTypeID.SelectedItem;
                DataRow row = AssemblyCheck.GetFocusedDataRow();
                frm_Edit_AssemblyCheck editForm = new frm_Edit_AssemblyCheck();
                editForm.checkType = item;
                editForm.serial = row[0].ToString();
                if (editForm.CreateForm())
                {
                    if (editForm.MappingData())
                    {
                        editForm.ShowDialog();
                        LoadData();
                    }
                    else
                    {
                        MessageBox.Show("Cannot edit selected row.");
                        editForm.Dispose();
                    }
                }
                else
                {
                    MessageBox.Show("Cannot edit selected row.");
                    editForm.Dispose();
                }
            }
        }

        void f4RV201ContextMenu_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.assemblyCheckMenu.Hide();            
            switch (e.ClickedItem.Name)
            {
                case "editSelection":
                    callEditForm();
                    break;
                case "addSelection":
                    callAddForm();
                    break;                
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void RV201_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.assemblyCheckMenu.Show(MousePosition);
        }

        private void RV201_DoubleClick(object sender, EventArgs e)
        {
            callEditForm();                        
        }
        
        //////////////////////////////////////////////////
        /// Load data from database to grid
        //////////////////////////////////////////////////
        private void LoadData()
        {
            AssemblyCheck.Columns.Clear();
            gridAssemblyCheck.DataSource = null;
            if (this.checktype > 0)
            {
                // Load list of Check Functions
                this.checkfunction.Rows.Clear();
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT * FROM [231_CheckFuntions]"
                        + " WHERE CheckTypeID = " + this.checktype.ToString() + " ORDER BY FunctionID";
                    SqlDataReader dataReader = sql_cmd.ExecuteReader();
                    while (dataReader.Read())
                    {
                        DataRow check_func = this.checkfunction.NewRow();
                        check_func["FunctionID"] = Int32.Parse(dataReader["FunctionID"].ToString().Trim());
                        check_func["CheckTypeID"] = Int32.Parse(dataReader["CheckTypeID"].ToString().Trim());
                        check_func["FunctionName"] = dataReader["FunctionName"].ToString().Trim();
                        this.checkfunction.Rows.Add(check_func);
                    }
                    dataReader.Close();
                    sql_cmd.Dispose();
                }
                catch
                {
                    MessageBox.Show("Cannot load Check Function");
                }
                finally
                {
                    myConnection.Close();
                }

                if (this.checkfunction.Rows.Count > 0)
                {
                    DataSet sourceDataSet = new DataSet();
                    SQLManagement mySQLManagement = new SQLManagement();
                    mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;

                    string sql = "SELECT";
                    for (int i = 0; i < this.checkfunction.Rows.Count; i++)
                    {
                        if (i == 0)
                        {
                            sql += " pvt.[" + this.checkfunction.Rows[i]["FunctionID"].ToString() + "]";
                        }
                        else
                        {
                            sql += ", pvt.[" + this.checkfunction.Rows[i]["FunctionID"].ToString() + "]";
                        }

                    }

                    sql += " FROM [231_CheckDetails] c"
                        + " PIVOT ( min(CheckValue)"
                        + " FOR CheckFuntionID IN (";
                    for (int i = 0; i < this.checkfunction.Rows.Count; i++)
                    {
                        if (i == 0)
                        {
                            sql += "[" + this.checkfunction.Rows[i]["FunctionID"].ToString() + "]";
                        }
                        else
                        {
                            sql += ", [" + this.checkfunction.Rows[i]["FunctionID"].ToString() + "]";
                        }

                    }

                    sql += ")"
                        + " ) pvt"
                        + " WHERE CheckTypeID = " + this.checktype.ToString();


                    sourceDataSet = mySQLManagement.SQL_Query(sql);

                    if (sourceDataSet != null)
                    {
                        DataTable ShowTable = sourceDataSet.Tables[0].Clone();
                        for (int i = 0; i < ShowTable.Columns.Count; i++)
                        {
                            ShowTable.Columns[i].DataType = typeof(string);
                        }
                        foreach (DataRow check_row in sourceDataSet.Tables[0].Rows)
                        {
                            ShowTable.ImportRow(check_row);
                        }

                        /*
                        foreach (DataRow show_row in ShowTable.Rows)
                        {
                            show_row.BeginEdit();
                            for (int i = 0; i < sourceDataSet.Tables[0].Columns.Count; i++)
                            {
                                if (string.IsNullOrEmpty(show_row[i].ToString().Trim()))
                                {
                                    show_row[i] = "***";
                                }
                            }
                            show_row.EndEdit();
                        }
                         * */
                        gridAssemblyCheck.DataSource = ShowTable;
                    }
                    else
                    {
                        DataTable newTable = new DataTable();
                        for (int i = 0; i < this.checkfunction.Rows.Count; i++)
                        {
                            newTable.Columns.Add(this.checkfunction.Rows[i]["FunctionName"].ToString(), typeof(string));
                        }

                        gridAssemblyCheck.DataSource = newTable;
                    }
                    for (int i = 0; i < this.checkfunction.Rows.Count; i++)
                    {
                        AssemblyCheck.Columns[i].Caption = this.checkfunction.Rows[i]["FunctionName"].ToString();
                    }
                    AssemblyCheck.BestFitColumns();
                }
                else
                {
                    MessageBox.Show("There are no function for selected Type");
                }
            }

        }

        private void bottonAdd_Click(object sender, EventArgs e)
        {
            callAddForm();
        }

        private void bottonEdit_Click(object sender, EventArgs e)
        {
            callEditForm();
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void buttonExport_Click(object sender, EventArgs e)
        {
            Cursor currentCursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                SaveFileDialog saveFDlg = new SaveFileDialog();
                saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                saveFDlg.Title = "Save As";
                saveFDlg.FileName = "AssemblyCheck" + this.checkTypeID.Text + ".xlsx";
                if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                {
                    if (!string.IsNullOrEmpty(saveFDlg.FileName))
                    {
                        /*
                        AssemblyCheck.OptionsPrint.PrintDetails = true;
                        AssemblyCheck.OptionsPrint.ExpandAllDetails = true;
                        AssemblyCheck.OptionsPrint.AutoWidth = false;
                        DevExpress.XtraPrinting.XlsxExportOptions myOptions = new DevExpress.XtraPrinting.XlsxExportOptions();                        
                        myOptions.TextExportMode = DevExpress.XtraPrinting.TextExportMode.Text;
                        myOptions.ShowGridLines = true;
                        AssemblyCheck.ExportToXlsx(saveFDlg.FileName, myOptions);
                        */
                        AssemblyCheck.OptionsPrint.PrintDetails = true;
                        AssemblyCheck.OptionsPrint.ExpandAllDetails = true;
                        AssemblyCheck.OptionsPrint.AutoWidth = false;
                        Font default_font = new Font("Times New Roman", 10);
                        AssemblyCheck.AppearancePrint.Row.Font = default_font;
                        AssemblyCheck.AppearancePrint.HeaderPanel.Font = default_font;
                        AssemblyCheck.OptionsPrint.UsePrintStyles = true;
                        //RV201F06.ExportToXlsx(saveFDlg.FileName);
                        DevExpress.XtraPrinting.XlsxExportOptions myOptions = new DevExpress.XtraPrinting.XlsxExportOptions();
                        myOptions.SheetName = "Data";
                        myOptions.TextExportMode = DevExpress.XtraPrinting.TextExportMode.Text;
                        myOptions.ShowGridLines = true;
                        AssemblyCheck.ExportToXlsx(saveFDlg.FileName, myOptions);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Warning!");
            }
            finally
            {
                Cursor.Current = currentCursor;
            }
        }

        private void checkTypeID_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.checkTypeID.SelectedIndex >= 0)
            {
                ComboboxItem item = (ComboboxItem)this.checkTypeID.SelectedItem;
                this.checktype = Int32.Parse(item.Value.ToString());
                LoadData();

                this.buttonExport.Enabled = true;
                this.buttonRefresh.Enabled = true;
                this.bottonAdd.Enabled = true;
                this.bottonEdit.Enabled = true;
            }
            else
            {
                AssemblyCheck.Columns.Clear();
                gridAssemblyCheck.DataSource = null;
                this.buttonExport.Enabled = false;
                this.buttonRefresh.Enabled = false;
                this.bottonAdd.Enabled = false;
                this.bottonEdit.Enabled = false;
            }
        }
    }
}
